//
//  YWMeterReadingDBManager.m
//  maintain
//
//  Created by kyo on 16/11/8.
//  Copyright © 2016年 ewin. All rights reserved.
//

#import "YWMeterReadingDBManager.h"
#import "YWDBTool.h"
#import "YWCommonTool.h"

#import "YWBuilding.h"
#import "YWThisMonthReadingMeterCount.h"

@implementation YWMeterReadingDBManager
YWSingletonM(Manager)

- (FMDatabase*)fmdb{
    return [[YWDBTool shareDBTool] fmdb];
}
- (FMDatabaseQueue*)dbQeue{
    return [[YWDBTool shareDBTool] dbQeue];
}

#pragma mark - 抄表记录(本地抄表记录，只增加不更新不减少)
- (BOOL)createMeterReadingRecordTable
{
    NSString* sql = @"CREATE TABLE 'meter_reading_record' (recordId text UNIQUE,readingTime timestamp,readingMonth text,equipmentId text,equipmentTypeId integer,thisReadNumber float,yearMonth integer,dateShow text,recordNote text,uploadStatus integer,yearMonthDay text,uniqueTag text,rate float,usedAmount float,price float,payment float);";
    return [[self fmdb] executeUpdate:sql];
}

#pragma mark - 抄表记录(服务端返回的抄表记录，每月每表的最新记录)
- (BOOL)createNetWorkMeterReadingRecordTable
{
    NSString* sql = @"CREATE TABLE 'meter_reading_record_netWork' (recordId text UNIQUE,readingTime timestamp,readingMonth text,equipmentId text,equipmentTypeId integer,thisReadNumber float,yearMonth integer,dateShow text,recordNote text,uploadStatus integer,yearMonthDay text,uniqueTag text,rate float,usedAmount float,price float,payment float);";
    return [[self fmdb] executeUpdate:sql];
}
- (BOOL)updateMeterReadingRecordWithRecordArray:(NSArray*)records
{
    __block BOOL executeResult = YES;
    __block NSArray* blockMeterReadingRecords = records;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockMeterReadingRecords enumerateObjectsUsingBlock:^(YWMeterReadingRecord* _Nonnull record, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = @"SELECT * FROM 'meter_reading_record' WHERE recordId=?;";
            FMResultSet* set = [db executeQuery:sql, record.meterRecordId];
            if ([set next]) {
                sql = @"UPDATE 'meter_reading_record' SET equipmentId=?, equipmentTypeId=?,readingTime=?,readingMonth=?,thisReadNumber=?,yearMonth=?,dateShow=? ,recordNote=?,uploadStatus=?,yearMonthDay =?,uniqueTag=?,rate=?,usedAmount=?,price=?,payment=? WHERE recordId = ?;";
                executeResult = [db executeUpdate:sql, record.equipmentId, @(record.equipmentTypeId), @(record.readingTime), record.readingMonth, @(record.thisReadNumber), @(record.yearMonth), record.dateShow, record.note, @(record.uploadStatus), record.yearMonthDay,record.uniqueTag,@(record.rate),@(record.usedAmount),@(record.price),@(record.payment), record.meterRecordId];
            }
            else {
                sql = @"INSERT INTO 'meter_reading_record' (recordId,equipmentId,equipmentTypeId,readingTime,readingMonth,thisReadNumber,yearMonth,dateShow,recordNote,uploadStatus,yearMonthDay,uniqueTag,rate,usedAmount,price,payment) VALUES (?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
                executeResult = [db executeUpdate:sql, record.meterRecordId, record.equipmentId, @(record.equipmentTypeId), @(record.readingTime), record.readingMonth, @(record.thisReadNumber), @(record.yearMonth), record.dateShow, record.note, @(record.uploadStatus), record.yearMonthDay,record.uniqueTag,@(record.rate),@(record.usedAmount),@(record.price),@(record.payment)];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
            
        }];
        [db closeOpenResultSets];
    }];
    NSMutableArray* meterReadingRecords = [NSMutableArray array];
    [blockMeterReadingRecords enumerateObjectsUsingBlock:^(YWMeterReadingRecord* _Nonnull record, NSUInteger idx, BOOL* _Nonnull stop) {
        [record.pictures enumerateObjectsUsingBlock:^(YWPatrolPicture* _Nonnull patrolPicture, NSUInteger idx, BOOL* _Nonnull stop) {
            patrolPicture.recordId = record.meterRecordId;
            patrolPicture.uploadStatus = YWPictureRecordUploadStatusSuccess;
            patrolPicture.recordType = YWPictureRecordTypeMeterReading;
        }];
        [meterReadingRecords addObjectsFromArray:record.pictures];
    }];
    [[YWDBTool shareDBTool] updatePatrolPictureWithNewWorkData:meterReadingRecords];
    return executeResult;
}

- (BOOL)updateNetWorkMeterReadingRecordWithRecordArray:(NSArray*)records
{
    __block BOOL executeResult = YES;
    __block NSArray* blockMeterReadingRecords = records;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockMeterReadingRecords enumerateObjectsUsingBlock:^(YWMeterReadingRecord* _Nonnull record, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = [NSString stringWithFormat:@"SELECT * FROM 'meter_reading_record_netWork' WHERE equipmentId = '%@' AND readingMonth = '%@';",record.equipmentId,record.readingMonth];
            FMResultSet* set = [db executeQuery:sql];
            if ([set next]) {
                sql = @"UPDATE 'meter_reading_record_netWork' SET equipmentTypeId=?,readingTime=?,yearMonth=?,thisReadNumber=?,dateShow=? ,recordNote=?,uploadStatus=?,yearMonthDay =?,uniqueTag=?,rate=?,usedAmount=?,price=?,payment=?,recordId = ? WHERE equipmentId=? AND readingMonth=?;";
                executeResult = [db executeUpdate:sql, @(record.equipmentTypeId), @(record.readingTime), @(record.yearMonth), @(record.thisReadNumber), record.dateShow, record.note, @(record.uploadStatus), record.yearMonthDay,record.uniqueTag,@(record.rate),@(record.usedAmount),@(record.price),@(record.payment), record.meterRecordId,record.equipmentId,record.readingMonth];
            }
            else {
                sql = @"INSERT INTO 'meter_reading_record_netWork' (recordId,equipmentId,equipmentTypeId,readingTime,readingMonth,thisReadNumber,yearMonth,dateShow,recordNote,uploadStatus,yearMonthDay,uniqueTag,rate,usedAmount,price,payment) VALUES (?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
                executeResult = [db executeUpdate:sql, record.meterRecordId, record.equipmentId, @(record.equipmentTypeId), @(record.readingTime), record.readingMonth, @(record.thisReadNumber), @(record.yearMonth), record.dateShow, record.note, @(record.uploadStatus), record.yearMonthDay,record.uniqueTag,@(record.rate),@(record.usedAmount),@(record.price),@(record.payment)];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
            
        }];
        [db closeOpenResultSets];
    }];
    NSMutableArray* meterReadingRecords = [NSMutableArray array];
    [blockMeterReadingRecords enumerateObjectsUsingBlock:^(YWMeterReadingRecord* _Nonnull record, NSUInteger idx, BOOL* _Nonnull stop) {
        [record.pictures enumerateObjectsUsingBlock:^(YWPatrolPicture* _Nonnull patrolPicture, NSUInteger idx, BOOL* _Nonnull stop) {
            patrolPicture.recordId = record.meterRecordId;
            patrolPicture.uploadStatus = YWPictureRecordUploadStatusSuccess;
            patrolPicture.recordType = YWPictureRecordTypeMeterReading;
        }];
        [meterReadingRecords addObjectsFromArray:record.pictures];
    }];
    [[YWDBTool shareDBTool] updatePatrolPictureWithNewWorkData:meterReadingRecords];
    return executeResult;
}

- (BOOL)updateMeterReadingRecordWithRecord:(YWMeterReadingRecord*)record
{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'meter_reading_record' WHERE recordId=?;";
        FMResultSet* set = [db executeQuery:sql, record.meterRecordId];
        if ([set next]) {
            sql = @"UPDATE 'meter_reading_record' SET equipmentId=?, equipmentTypeId=?,readingTime=?,readingMonth=?,thisReadNumber=?,yearMonth=?,dateShow=? ,recordNote=?,uploadStatus=?,yearMonthDay=?,uniqueTag=?,rate=?,usedAmount=?,price=?,payment=? WHERE recordId = ?;";
            executeResult = [db executeUpdate:sql, record.equipmentId, @(record.equipmentTypeId), @(record.readingTime), record.readingMonth, @(record.thisReadNumber), @(record.yearMonth), record.dateShow, record.note, @(record.uploadStatus), record.yearMonthDay,record.uniqueTag,@(record.rate),@(record.usedAmount),@(record.price),@(record.payment), record.meterRecordId];
        }
        else {
            sql = @"INSERT INTO 'meter_reading_record' (recordId,equipmentId,equipmentTypeId,readingTime,readingMonth,thisReadNumber,yearMonth,dateShow,recordNote,uploadStatus,yearMonthDay,uniqueTag,rate,usedAmount,price,payment) VALUES (?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
            executeResult = [db executeUpdate:sql, record.meterRecordId, record.equipmentId, @(record.equipmentTypeId), @(record.readingTime), record.readingMonth, @(record.thisReadNumber), @(record.yearMonth), record.dateShow, record.note, @(record.uploadStatus), record.yearMonthDay,record.uniqueTag,@(record.rate),@(record.usedAmount),@(record.price),@(record.payment)];
        }
        [db closeOpenResultSets];
    }];
    
    return executeResult;
}

- (NSArray*)meterReadingRecordWithEquipmentType:(NSInteger)meterEquipmentType page:(NSInteger)page
{
    __block NSInteger showCount = page * 20;
    __block NSMutableArray* totalMeterReadingRecords = [NSMutableArray array];
    __block NSMutableArray* recordDate = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = [NSString stringWithFormat:@"SELECT * FROM 'meter_reading_record_netWork' WHERE equipmentTypeId=%ld ORDER BY readingTime DESC LIMIT %ld",(long)meterEquipmentType,(long)showCount];
        FMResultSet* set = [db executeQuery:sql];
        while ([set next]) {
            YWMeterReadingRecord* record = [self meterReadingRecordWithFMResultSet:set FMDatabase:db];
            [totalMeterReadingRecords addObject:record];
            if (![recordDate containsObject:@(record.yearMonth)]) {
                [recordDate addObject:@(record.yearMonth)];
            }
        }
        [db closeOpenResultSets];
    }];
    NSMutableArray* myRecords = [NSMutableArray array];
    for (int i = 0; i < recordDate.count; i++) {
        NSMutableArray* tempArray = [NSMutableArray array];
        for (YWMeterReadingRecord* record in totalMeterReadingRecords) {
            if ([recordDate[i] integerValue] == record.yearMonth) {
                [tempArray addObject:record];
            }
        }
        if (tempArray.count) {
            [myRecords addObject:tempArray];
        }
    }
    return myRecords;
}

- (YWMeterReadingRecord*)meterReadingRecordWithRecordId:(NSString*)recordId
{
    __block YWMeterReadingRecord* record = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'meter_reading_record' WHERE recordId=?";
        FMResultSet* set = [db executeQuery:sql, recordId];
        while ([set next]) {
            record = [self meterReadingRecordWithFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    return record;
}

- (YWMeterReadingRecord*)latestMeterReadingRecordWithEquipmentId:(NSString*)equipmentId{
    __block YWMeterReadingRecord* record = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'meter_reading_record' WHERE equipmentId=? AND uploadStatus=? ORDER BY readingTime DESC LIMIT 1";
        FMResultSet* set = [db executeQuery:sql, equipmentId,@(YWMeterReadingRecordUploadStatusFinished)];
        if ([set next]) {
            record = [self meterReadingRecordWithFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    return record;
}

- (BOOL)modifyMeterReadingRecordIdWithRecord:(YWMeterReadingRecord*)record recordId:(NSString*)recordId
{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"UPDATE 'meter_reading_record' SET recordId=? WHERE recordId=?;";
        executeResult = [db executeUpdate:sql, recordId, record.meterRecordId];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)updateMeterReadingRecordUploadStatus:(YWMeterReadingRecordUploadStatus)uploadStatus recordId:(NSString*)recordId
{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"UPDATE 'meter_reading_record' SET uploadStatus=? WHERE recordId=?;";
        executeResult = [db executeUpdate:sql, @(uploadStatus), recordId];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)deleteMeterReadingRecordWithEquipmentType:(NSInteger)meterEquipmentType{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"DELETE FROM 'meter_reading_record_netWork' WHERE equipmentTypeId=?;";
        executeResult = [db executeUpdate:sql,@(meterEquipmentType)];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (NSArray*)meterReadingRecordWithUploadStatusFail
{
    __block NSMutableArray* records = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'meter_reading_record' WHERE uploadStatus=? or uploadStatus=?";
        FMResultSet* set = [db executeQuery:sql, @(YWMeterReadingRecordUploadStatusUnFinished),@(YWMeterReadingRecordUploadStatusUploading)];
        while ([set next]) {
            YWMeterReadingRecord* record = [self meterReadingRecordWithFMResultSet:set FMDatabase:db];
            [records addObject:record];
        }
        [db closeOpenResultSets];
    }];
    return records;
}

- (YWMeterReadingRecord*)meterReadingRecordWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWMeterReadingRecord* meterReadingRecord = [[YWMeterReadingRecord alloc] init];
    meterReadingRecord.meterRecordId = [set stringForColumn:@"recordId"];
    meterReadingRecord.readingTime = [set longLongIntForColumn:@"readingTime"];
    meterReadingRecord.readingMonth = [set stringForColumn:@"readingMonth"];
    meterReadingRecord.equipmentId = [set stringForColumn:@"equipmentId"];
    meterReadingRecord.equipmentTypeId = [set intForColumn:@"equipmentTypeId"];
    //    meterReadingRecord.lastReadNumber = [set doubleForColumn:@"lastReadNumber"];
    //    meterReadingRecord.lastReadTime = [set longLongIntForColumn:@"lastReadTime"];
    meterReadingRecord.thisReadNumber = [set doubleForColumn:@"thisReadNumber"];
    meterReadingRecord.yearMonth = [set intForColumn:@"yearMonth"];
    meterReadingRecord.dateShow = [set stringForColumn:@"dateShow"];
    meterReadingRecord.note = [set stringForColumn:@"recordNote"];
    meterReadingRecord.uploadStatus = [set intForColumn:@"uploadStatus"];
    meterReadingRecord.yearMonthDay = [set stringForColumn:@"yearMonthDay"];
    meterReadingRecord.uniqueTag = [set stringForColumn:@"uniqueTag"];
    
    meterReadingRecord.rate = [set doubleForColumn:@"rate"];
    meterReadingRecord.usedAmount = [set doubleForColumn:@"usedAmount"];
    meterReadingRecord.price = [set doubleForColumn:@"price"];
    meterReadingRecord.payment = [set doubleForColumn:@"payment"];
    return meterReadingRecord;
}

#pragma mark - create thisMonth_readmeter_count Table
/**
 *  名称 本月抄表数记录表 thismonth_readmeter_count
 */
- (BOOL)createThisMonthReadMeterCountTable
{
    NSString* sql = @"CREATE TABLE 'thismonth_readmeter_count' (buildingId text,equipmentTypeId integer,total integer,recordedAmount integer);";
    return [[self fmdb] executeUpdate:sql];
}

- (BOOL)saveThisMonthReadMeterCountWithJson:(NSArray*)jsons
{
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        __block NSString* sql = nil;
        sql = @"DELETE FROM 'thismonth_readmeter_count';";
        executeResult = [db executeUpdate:sql];
        if (!executeResult) {
            *rollback = YES;
        }
        
        [jsons enumerateObjectsUsingBlock:^(YWThisMonthReadingMeterCountJson* _Nonnull json, NSUInteger idx, BOOL* _Nonnull stop) {
            
            [json.meterStatistics enumerateObjectsUsingBlock:^(YWMeterStatistics* _Nonnull meterStatistics, NSUInteger idx, BOOL* _Nonnull stop) {
                sql = @"INSERT INTO 'thismonth_readmeter_count' (buildingId,equipmentTypeId,total,recordedAmount) VALUES (?, ?, ?,?);";
                executeResult = [db executeUpdate:sql, json.building.buildingId, @(meterStatistics.equipmentType.equipmentTypeId), @(meterStatistics.total), @(meterStatistics.recordedAmount)];
                if (!executeResult) {
                    *rollback = YES;
                    *stop = YES;
                }
            }];
            
            if (!executeResult) {
                *stop = YES;
            }
        }];
        
        [db closeOpenResultSets];
    }];
    
    return executeResult;
}

- (NSArray*)allThisMonthReadMeterCount:(NSInteger)equipmenttypeId
{
    __block NSMutableArray* thisMonthReadMeterCounts = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'thismonth_readmeter_count' where equipmentTypeId=?";
        FMResultSet* set = [db executeQuery:sql, @(equipmenttypeId)];
        while ([set next]) {
            YWThisMonthReadingMeterCount* thisMonthReadingMeterCount = [[YWThisMonthReadingMeterCount alloc] init];
            thisMonthReadingMeterCount.buildingId = [set stringForColumn:@"buildingId"];
            thisMonthReadingMeterCount.equipmentTypeId = [set intForColumn:@"equipmentTypeId"];
            thisMonthReadingMeterCount.total = [set intForColumn:@"total"];
            thisMonthReadingMeterCount.recordedAmount = [set intForColumn:@"recordedAmount"];
            [thisMonthReadMeterCounts addObject:thisMonthReadingMeterCount];
        }
        [db closeOpenResultSets];
    }];
    
    return thisMonthReadMeterCounts.count ? thisMonthReadMeterCounts : nil;
}

- (NSInteger)toDayReadMeterCountWithEquipmentTypeId:(NSInteger)equipmentTypeId
{
    __block NSInteger toDayReadMeterCount = 0;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT count(*) as recordCount FROM 'meter_reading_record' where equipmentTypeId=? and yearMonthDay = ?";
        NSString* nowDate = [YWCommonTool showDate:[[NSDate date] timeIntervalSince1970]];
        FMResultSet* set = [db executeQuery:sql, @(equipmentTypeId), nowDate];
        if ([set next]) {
            toDayReadMeterCount = [set intForColumn:@"recordCount"];
        }
        [db closeOpenResultSets];
    }];
    return toDayReadMeterCount;
}

- (BOOL)modifyReadNumberOfMeterReadingRecord:(YWMeterReadingRecord*)record
{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"UPDATE 'meter_reading_record' SET thisReadNumber=? WHERE recordId=?;";
        executeResult = [db executeUpdate:sql, @(record.thisReadNumber), record.meterRecordId];
        [db closeOpenResultSets];
    }];
    return executeResult;
}
@end
